# PROCEDURE_SPLITTER
drop PROCEDURE IF EXISTS p_inventory;
# PROCEDURE_SPLITTER
CREATE PROCEDURE p_inventory()   
 BEGIN    
    DECLARE t_goods_id CHAR(14); 
    DECLARE t_color_id   CHAR (14); 
    DECLARE t_size_id   CHAR (14);  
    DECLARE t_inv_id INTEGER (4);      
 
 DECLARE onhand DECIMAL(15,4); 
 DECLARE finished INTEGER DEFAULT 0; 
 DECLARE oh_cur CURSOR FOR 
      SELECT goods_id,color_id,size_id,id FROM open_goods_inventory where goods_id in (select distinct(goods_id) from open_order_goods,open_order_info
		 where open_order_info.shipping_status = 0 and open_order_info.order_status = 1 and open_order_goods.order_id = open_order_info.order_id ); 
		 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;

   OPEN oh_cur; 

  calcloop:  
      LOOP 
      FETCH oh_cur INTO t_goods_id,t_color_id,t_size_id,t_inv_id;  
	
	IF finished=1 THEN 
		LEAVE calcloop; 
	END IF;       
    UPDATE open_goods_inventory SET open_goods_inventory.goods_number_available  = open_goods_inventory.goods_number_actual - 
    ( select ifnull( count(open_order_goods.goods_number),0)  from open_order_goods,open_order_info
		 where open_order_info.shipping_status = 0 and open_order_info.order_status = 1
         and open_order_goods.order_id = open_order_info.order_id 
		 and open_order_goods.goods_id = t_goods_id
		 and open_order_goods.color_id = t_color_id
		 and open_order_goods.size_id = t_size_id
		)
    WHERE id = t_inv_id  ;  
  END LOOP calcloop; 

     UPDATE open_goods_inventory SET open_goods_inventory.goods_number_available  = open_goods_inventory.goods_number_actual
     where goods_id not in (select distinct(goods_id) from open_order_goods,open_order_info
		 where open_order_info.shipping_status = 0 and open_order_info.order_status = 1 and open_order_goods.order_id = open_order_info.order_id );  

  CLOSE oh_cur;     
END